/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mpinventory;

import java.sql.*;
import java.util.Date;
import java.util.List;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
/**
 *
 * @author user
 */
public class TransactionPurchaseQuery {
    private Connection conn;
    public TransactionRelease release=new TransactionRelease();
    public TransactionPurchaseQuery(Connection conn){
        this.conn=conn;
    }
    public List<Item> initializeItems(){
        List<Item> items=new ArrayList<Item>();
        try{
            Statement stmt=conn.createStatement();
            String query="select * from inventoryitemdata ORDER BY itemName ASC";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next()){
                Item item=new Item();
                item.setID(rs.getInt("itemID"));
                item.setQuantity(rs.getFloat("itemQuantity"));
                item.setQuantityUnit(rs.getString("itemQuantityUnit"));
                item.setDescription(rs.getString("itemName"));
                item.setUnitPrice(rs.getFloat("itemPrice"));
                items.add(item);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return items;
    }
    public void addPurchase(int purchaseID, int supplierID, String supplierName, Date purchaseDate, String purchaseStatus, List<Float> purchaseQuantity, List<Integer> itemID, List<String> itemName, List<Float> purchasePrice){
        String query="insert into companypurchasedata(purchaseID, supplierID, supplierName, purchaseDate, purchaseStatus) values(?,?,?,?,?)";
        java.sql.Date date=new java.sql.Date(purchaseDate.getTime());
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, purchaseID);
            prest.setInt(2, supplierID);
            prest.setString(3, supplierName);
            prest.setDate(4, date);
            prest.setString(5, purchaseStatus);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully added Purchase " + purchaseID + ": " + supplierName + " to the database!");
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        query="insert into companypurchase(ID, purchaseQuantity, itemID, itemName, purchasePrice) values(?,?,?,?,?)";
        int i=0;
        int ctr=0;
        int transactionPurchaseID=setTransactionPurchaseID();
        while(!itemID.isEmpty()){
            transactionPurchaseID+=ctr;
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setInt(1, transactionPurchaseID);
                prest.setFloat(2, purchaseQuantity.get(i));
                prest.setInt(3, itemID.get(i));
                prest.setString(4, itemName.get(i));
                prest.setFloat(5, purchasePrice.get(i));
                prest.executeUpdate();
                String query2="insert into companypurchasewe(ID, purchaseID) values(?,?)";
                try{
                    PreparedStatement prest2=conn.prepareStatement(query2);
                    prest2.setInt(1, transactionPurchaseID);
                    prest2.setInt(2, purchaseID);
                    prest2.executeUpdate();
                }
                catch(SQLException ex){
                    Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
                purchaseQuantity.remove(i);
                itemID.remove(i);
                itemName.remove(i);
                purchasePrice.remove(i);
                ctr++;
            }
            catch(SQLException ex){
                Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    public List<TransactionPurchase> initializePurchases(){
        List<TransactionPurchase> purchases=new ArrayList<TransactionPurchase>();
        List<Integer> IDs=new ArrayList<Integer>();
        List<Integer> itemIDs=new ArrayList<Integer>();
        List<String> descriptions=new ArrayList<String>();
        List<Float> quantities=new ArrayList<Float>();
        List<String> units=new ArrayList<String>();
        List<Float> prices=new ArrayList<Float>();
        try{
            Statement stmt=conn.createStatement();
            String query="SELECT * FROM companypurchase NATURAL JOIN companypurchasewe NATURAL JOIN companypurchasedata NATURAL JOIN inventoryitemdata NATURAL JOIN supplierdata ORDER BY purchaseID DESC";
            ResultSet rs=stmt.executeQuery(query);
            int purchaseID=0;
            TransactionPurchase purchase=new TransactionPurchase();
            int i=0;
            while(rs.next()){
                if(purchaseID!=rs.getInt("purchaseID")){
                    if(i!=0){
                        purchase.setIDs(IDs);
                        purchase.setItemIDs(itemIDs);
                        purchase.setItemNames(descriptions);
                        purchase.setQuantities(quantities);
                        purchase.setUnits(units);
                        purchase.setPrices(prices);
                        purchases.add(purchase);
                    }
                    purchase=new TransactionPurchase();
                    purchaseID=rs.getInt("purchaseID");
                    purchase.setPurchaseID(purchaseID);
                    purchase.setSupplierID(rs.getInt("supplierID"));
                    purchase.setSupplierName(rs.getString("supplierName"));
                    purchase.setDate(rs.getDate("purchaseDate"));
                    purchase.setStatus(rs.getString("purchaseStatus"));
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("purchaseQuantity"));
                    units.add(rs.getString("itemQuantityUnit"));
                    prices.add(rs.getFloat("purchasePrice"));
                    i=1;
                }
                else{
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("purchaseQuantity"));
                    units.add(rs.getString("itemQuantityUnit"));
                    prices.add(rs.getFloat("purchasePrice"));
                }
            }
            purchase.setIDs(IDs);
            purchase.setItemIDs(itemIDs);
            purchase.setItemNames(descriptions);
            purchase.setQuantities(quantities);
            purchase.setUnits(units);
            purchase.setPrices(prices);
            purchases.add(purchase);
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return purchases;
    }
    public void editPurchase(int purchaseID, int supplierID, String supplierName, Date purchaseDate, String purchaseStatus, List<Float> purchaseQuantity, List<Integer> itemID, List<String> itemName, List<Float> purchasePrice, List<Integer> origID){
        String query="delete from companypurchasewe where ID=? AND purchaseID=?";
        int i=0;
        if(purchaseStatus.equals("Delivered"))
            updateInventory(purchaseQuantity,purchasePrice,itemID);
        while(i!=origID.size())
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setInt(1, origID.get(i));
                prest.setInt(2, purchaseID);
                prest.executeUpdate();
                i++;
            }catch(SQLException ex){}
        query="delete from companypurchase where ID=?";
        i=0;
        while(i!=origID.size())
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setInt(1, origID.get(i));
                prest.executeUpdate();
                i++;
            }catch(SQLException ex){}

        query="update companypurchasedata set supplierID=?, supplierName=?, purchaseDate=?, purchaseStatus=? where purchaseID=?";
        java.sql.Date date=new java.sql.Date(purchaseDate.getTime());
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, supplierID);
            prest.setString(2, supplierName);
            prest.setDate(3, date);
            prest.setString(4, purchaseStatus);
            prest.setInt(5, purchaseID);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully edited Purchase " + purchaseID + ": " + supplierName + "!");
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        query="insert into companypurchase(ID, purchaseQuantity, itemID, itemName, purchasePrice) values(?,?,?,?,?)";
        i=0;
        int ctr=0;
        int transactionPurchaseID=setTransactionPurchaseID();
        while(!itemID.isEmpty()){
            transactionPurchaseID+=ctr;
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setInt(1, transactionPurchaseID);
                prest.setFloat(2, purchaseQuantity.get(i));
                prest.setInt(3, itemID.get(i));
                prest.setString(4, itemName.get(i));
                prest.setFloat(5, purchasePrice.get(i));
                prest.executeUpdate();
                String query2="insert into companypurchasewe(ID, purchaseID) values(?,?)";
                try{
                    PreparedStatement prest2=conn.prepareStatement(query2);
                    prest2.setInt(1, transactionPurchaseID);
                    prest2.setInt(2, purchaseID);
                    prest2.executeUpdate();
                }
                catch(SQLException ex){
                    Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
                purchaseQuantity.remove(i);
                itemID.remove(i);
                itemName.remove(i);
                purchasePrice.remove(i);
                ctr++;
            }
            catch(SQLException ex){
                Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    public TransactionPurchase search(int purchaseID){
        TransactionPurchase purchase=new TransactionPurchase();
        List<Integer> IDs=new ArrayList<Integer>();
        List<Integer> itemIDs=new ArrayList<Integer>();
        List<String> descriptions=new ArrayList<String>();
        List<Float> quantities=new ArrayList<Float>();
        List<String> units=new ArrayList<String>();
        List<Float> prices=new ArrayList<Float>();
        try{
            Statement stmt=conn.createStatement();
            String query="SELECT * FROM companypurchase NATURAL JOIN companypurchasewe NATURAL JOIN companypurchasedata NATURAL JOIN inventoryitemdata NATURAL JOIN supplierdata where purchaseID=?";
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, purchaseID);
            ResultSet rs=prest.executeQuery();
            while(rs.next()){
                purchase.setPurchaseID(rs.getInt("purchaseID"));
                purchase.setSupplierID(rs.getInt("supplierID"));
                purchase.setSupplierName(rs.getString("supplierName"));
                purchase.setDate(rs.getDate("purchaseDate"));
                purchase.setStatus(rs.getString("purchaseStatus"));
                IDs.add(rs.getInt("ID"));
                itemIDs.add(rs.getInt("itemID"));
                descriptions.add(rs.getString("itemName"));
                quantities.add(rs.getFloat("purchaseQuantity"));
                units.add(rs.getString("itemQuantityUnit"));
                prices.add(rs.getFloat("purchasePrice"));
            }
            purchase.setIDs(IDs);
            purchase.setItemIDs(itemIDs);
            purchase.setItemNames(descriptions);
            purchase.setQuantities(quantities);
            purchase.setUnits(units);
            purchase.setPrices(prices);
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return purchase;
    }
    public List<TransactionPurchase> search(String status){
        List<TransactionPurchase> purchases=new ArrayList<TransactionPurchase>();
        List<Integer> IDs=new ArrayList<Integer>();
        List<Integer> itemIDs=new ArrayList<Integer>();
        List<String> descriptions=new ArrayList<String>();
        List<Float> quantities=new ArrayList<Float>();
        List<String> units=new ArrayList<String>();
        List<Float> prices=new ArrayList<Float>();
        try{
            Statement stmt=conn.createStatement();
            String query="SELECT * FROM companypurchase NATURAL JOIN companypurchasewe NATURAL JOIN companypurchasedata NATURAL JOIN inventoryitemdata NATURAL JOIN supplierdata WHERE purchaseStatus=? ORDER BY purchaseID DESC";
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, status);
            ResultSet rs=prest.executeQuery();
            int purchaseID=0;
            TransactionPurchase purchase=new TransactionPurchase();
            int i=0;
            while(rs.next()){
                if(purchaseID!=rs.getInt("purchaseID")){
                    if(i!=0){
                        purchase.setIDs(IDs);
                        purchase.setItemIDs(itemIDs);
                        purchase.setItemNames(descriptions);
                        purchase.setQuantities(quantities);
                        purchase.setUnits(units);
                        purchase.setPrices(prices);
                        purchases.add(purchase);
                    }
                    purchase=new TransactionPurchase();
                    purchaseID=rs.getInt("purchaseID");
                    purchase.setPurchaseID(purchaseID);
                    purchase.setSupplierID(rs.getInt("supplierID"));
                    purchase.setSupplierName(rs.getString("supplierName"));
                    purchase.setDate(rs.getDate("purchaseDate"));
                    purchase.setStatus(rs.getString("purchaseStatus"));
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("purchaseQuantity"));
                    units.add(rs.getString("itemQuantityUnit"));
                    prices.add(rs.getFloat("purchasePrice"));
                    i=1;
                }
                else{
                    IDs.add(rs.getInt("ID"));
                    itemIDs.add(rs.getInt("itemID"));
                    descriptions.add(rs.getString("itemName"));
                    quantities.add(rs.getFloat("purchaseQuantity"));
                    units.add(rs.getString("itemQuantityUnit"));
                    prices.add(rs.getFloat("purchasePrice"));
                }
            }
            purchase.setIDs(IDs);
            purchase.setItemIDs(itemIDs);
            purchase.setItemNames(descriptions);
            purchase.setQuantities(quantities);
            purchase.setUnits(units);
            purchase.setPrices(prices);
            purchases.add(purchase);
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return purchases;
    }
    public List<TransactionPurchase> searchItem(String itemName){
        List<TransactionPurchase> purchases=new ArrayList<TransactionPurchase>();
        try{
            Statement stmt=conn.createStatement();
            String query="SELECT * FROM companypurchase NATURAL JOIN companypurchasewe NATURAL JOIN companypurchasedata WHERE itemName=? ORDER BY purchaseDate DESC";
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, itemName);
            ResultSet rs=prest.executeQuery();
            TransactionPurchase purchase=new TransactionPurchase();
            while(rs.next()){
                purchase=new TransactionPurchase();
                purchase.setPurchaseID(rs.getInt("purchaseID"));
                purchase.setDate(rs.getDate("purchaseDate"));
                purchase.setPrice(rs.getFloat("purchasePrice"));
                purchases.add(purchase);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return purchases;
    }
    public void updateInventory(List<Float> purchaseQuantity, List<Float> purchasePrice, List<Integer> itemID){
        String query="update inventoryitemdata set itemQuantity=itemQuantity+?, itemPrice=? where itemID=?";
        for(int i=0;i<itemID.size();i++)
            try{
                PreparedStatement prest=conn.prepareStatement(query);
                prest.setFloat(1, purchaseQuantity.get(i));
                prest.setFloat(2, purchasePrice.get(i));
                prest.setInt(3, itemID.get(i));
                prest.executeUpdate();
            }
            catch(SQLException ex){
                Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
    }
    public int setID(){
        try{
            Statement stmt=conn.createStatement();
            String query="select max(purchaseID) as maxID from companypurchasedata";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next())
                return rs.getInt("maxID")+1;
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    public int setTransactionPurchaseID(){
        try{
            Statement stmt=conn.createStatement();
            String query="select max(ID) as maxID from companypurchase";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next())
                return rs.getInt("maxID")+1;
        }
        catch(SQLException ex){
            Logger.getLogger(TransactionPurchaseQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
}
